InnoDB inplace-update加锁流程分析

线上遇到一起死锁问题，一条DELETE语句与一条UPDATE语句产生了死锁，经过和印风的讨论分析找到原因：DELETE语句通过二级索引删除记录，加锁顺序：二级索引（WHERE使用到二级索引）–>主键索引 –> 所有其它二级索引，UPDATE语句的加锁顺序：二级索引（WHERE条件使用二级索引）–>主键索引 –>包含更新字段的其它二级索引，由于DELETE操作更新了UPDATE语句WHERE条件使用到的索引，这导致DELETE与UPDATE加锁顺序相反，导致死锁

为了进一步研究InnoDB update操作加锁流程，进行了下列实验（update操作为inplace-update），关于mysql update的上层调用可以参考: MySQL update语法SQL解析源码分析 和 Oracle/PostgreSQL UPDATE…RETURNING…在MySQL中的实现

实验数据

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a_b` (`a`,`b`),
  KEY `idx_b` (`b`)
) ENGINE=InnoDB
1 row in set (12.27 sec)
 
mysql> select * from t;
+----+------+------+------+
| id | a    | b    | c    |
+----+------+------+------+
|  1 | 6546 | 6238 | 1551 |
|  2 | 9042 |  558 | 5664 |
|  3 | 6644 | 6230 | 1216 |
|  4 | 7391 | 3308 | 4365 |
|  5 | 1900 | 6408 | 6337 |
|  6 | 2461 | 3296 | 9096 |
|  7 | 5593 |  676 | 6600 |
|  8 |  972 | 5062 | 2391 |
|  9 | 6773 | 6688 | 3123 |
| 10 | 5550 | 8383 | 5266 |
| 11 | 1181 |   93 | 6932 |
| 12 | 4378 | 1097 | 2351 |
| 13 | 8461 | 5255 |  891 |
| 14 | 8690 |  775 | 7808 |
| 15 | 6712 |  137 |  549 |
| 16 | 2335 |   27 | 3128 |
+----+------+------+------+
16 rows in set (0.00 sec)

update语句：

update t set a=a+1 where b=93;

read阶段1：

row_search_for_mysql，对找到的二级索引记录加 LOCK_X（LOCK_ORDINARY）锁（index->name=idx_b）

#0  lock_rec_lock (impl=0, mode=3, block=0x7f4543967d00, heap_no=3, index=0x7f4520023b68, thr=0x7f452000dd68)
    at storage/innobase/lock/lock0lock.c:2118
#1  0x0000000000917397 in lock_sec_rec_read_check_and_lock (flags=<value optimized out>, block=0x7f4543967d00, rec=0x7f45c1af408c "\200", index=0x7f4520023b68, 
    offsets=0x7f452db6ff80, mode=<value optimized out>, gap_mode=0, thr=0x7f452000dd68)
    at storage/innobase/lock/lock0lock.c:5477
#2  0x0000000000856137 in sel_set_rec_lock (block=0x7f4543967d00, rec=0x7f45c1af408c "\200", index=0x7f4520023b68, offsets=0x7f452db6ff80, 
    mode=<value optimized out>, type=<value optimized out>, thr=0x7f452000dd68)
    at storage/innobase/row/row0sel.c:1007
#3  0x000000000085937d in row_search_for_mysql (buf=0x7f452000bf20 "\377", mode=2, prebuilt=<value optimized out>, match_mode=1, direction=<value optimized out>)
    at storage/innobase/row/row0sel.c:4280
#4  0x00000000008306a5 in ha_innobase::index_read (this=0x7f45200085d0, buf=0x7f452000bf20 "\377", key_ptr=0x7f452002a198 "", key_len=5, 
    find_flag=<value optimized out>) at storage/innobase/handler/ha_innodb.cc:6477
#5  0x000000000068d69f in handler::read_range_first (this=0x7f45200085d0, start_key=<value optimized out>, end_key=<value optimized out>, 
    eq_range_arg=<value optimized out>, sorted=<value optimized out>) at sql/handler.cc:4527
#6  0x000000000068af26 in handler::read_multi_range_first (this=0x7f45200085d0, found_range_p=0x7f452db70418, ranges=<value optimized out>, 
    range_count=<value optimized out>, sorted=<value optimized out>, buffer=<value optimized out>)
    at sql/handler.cc:4401
#7  0x00000000007451b6 in QUICK_RANGE_SELECT::get_next (this=0x7f4520023370) at sql/opt_range.cc:8711
#8  0x000000000075f12b in rr_quick (info=0x7f452db70700) at sql/records.cc:344
#9  0x00000000005f7b36 in mysql_update (thd=0x6e01ad0, table_list=0x7f4520004cb0, fields=..., values=..., conds=0x7f45200056a0, order_num=<value optimized out>, 
    order=0x7f452db70700, limit=18446744073709551615, handle_duplicates=DUP_ERROR, ignore=false, found_return=0x7f452db713d0, updated_return=0x7f452db713c8)
    at sql/sql_update.cc:644

read阶段2：

row_search_for_mysql，对找到的主键索引记录加 LOCK_X（LOCK_REC_NOT_GAP）锁（index->name=PRIMARY）

#0  lock_rec_lock (impl=0, mode=1027, block=0x7f454396c840, heap_no=12, index=0x7f452000d238, thr=0x7f452000dd68)
    at storage/innobase/lock/lock0lock.c:2118
#1  0x0000000000917089 in lock_clust_rec_read_check_and_lock (flags=<value optimized out>, block=0x7f454396c840, rec=0x7f45c1ba01dc "\200", index=0x7f452000d238, 
    offsets=<value optimized out>, mode=<value optimized out>, gap_mode=1024, thr=0x7f452000dd68)
    at storage/innobase/lock/lock0lock.c:5551
#2  0x000000000085a0f8 in row_sel_get_clust_rec_for_mysql (buf=0x7f452000bf20 "\377", mode=2, prebuilt=<value optimized out>, match_mode=1, 
    direction=<value optimized out>) at storage/innobase/row/row0sel.c:2976
#3  row_search_for_mysql (buf=0x7f452000bf20 "\377", mode=2, prebuilt=<value optimized out>, match_mode=1, direction=<value optimized out>)
    at storage/innobase/row/row0sel.c:4478
#4  0x00000000008306a5 in ha_innobase::index_read (this=0x7f45200085d0, buf=0x7f452000bf20 "\377", key_ptr=0x7f452002a198 "", key_len=5, 
    find_flag=<value optimized out>) at storage/innobase/handler/ha_innodb.cc:6477
#5  0x000000000068d69f in handler::read_range_first (this=0x7f45200085d0, start_key=<value optimized out>, end_key=<value optimized out>, 
    eq_range_arg=<value optimized out>, sorted=<value optimized out>) at sql/handler.cc:4527
#6  0x000000000068af26 in handler::read_multi_range_first (this=0x7f45200085d0, found_range_p=0x7f452db70418, ranges=<value optimized out>, 
    range_count=<value optimized out>, sorted=<value optimized out>, buffer=<value optimized out>)
    at sql/handler.cc:4401
#7  0x00000000007451b6 in QUICK_RANGE_SELECT::get_next (this=0x7f4520023370) at sql/opt_range.cc:8711
#8  0x000000000075f12b in rr_quick (info=0x7f452db70700) at sql/records.cc:344
#9  0x00000000005f7b36 in mysql_update (thd=0x6e01ad0, table_list=0x7f4520004cb0, fields=..., values=..., conds=0x7f45200056a0, order_num=<value optimized out>, 
    order=0x7f452db70700, limit=18446744073709551615, handle_duplicates=DUP_ERROR, ignore=false, found_return=0x7f452db713d0, updated_return=0x7f452db713c8)
    at sql/sql_update.cc:644

update阶段1：

row_upd_clust_step，更新主键索引记录

#0  row_upd_clust_step (node=0x7f4520022258, thr=0x7f4520022558) at storage/innobase/row/row0upd.c:2127
#1  0x000000000086217e in row_upd (thr=0x7f4520022558) at storage/innobase/row/row0upd.c:2317
#2  row_upd_step (thr=0x7f4520022558) at storage/innobase/row/row0upd.c:2457
#3  0x000000000084ca71 in row_update_for_mysql (mysql_rec=<value optimized out>, prebuilt=0x7f452000b328)
    at storage/innobase/row/row0mysql.c:1462
#4  0x00000000008328b1 in ha_innobase::update_row (this=0x7f45200085d0, old_row=0x7f452000bf38 "\361\v", new_row=0x7f452000bf20 "\361\v")
    at storage/innobase/handler/ha_innodb.cc:6042
#5  0x000000000068e48a in handler::ha_update_row (this=0x7f45200085d0, old_data=0x7f452000bf38 "\361\v", new_data=0x7f452000bf20 "\361\v")
    at sql/handler.cc:5031
#6  0x00000000005f8139 in mysql_update (thd=0x6e01ad0, table_list=0x7f4520004cb0, fields=..., values=..., conds=0x7f45200056a0, order_num=<value optimized out>, 
    order=0x7f452db70700, limit=18446744073709551615, handle_duplicates=DUP_ERROR, ignore=false, found_return=0x7f452db713d0, updated_return=0x7f452db713c8)
    at sql/sql_update.cc:713

update阶段2：

row_upd_sec_step，更新二级索引记录（node->index->name = idx_a_b）

#0  row_upd_sec_index_entry (node=0x7f4520022258, thr=0x7f4520022558) at storage/innobase/row/row0upd.c:1583
#1  0x0000000000862337 in row_upd_sec_step (thr=0x7f4520022558) at storage/innobase/row/row0upd.c:1710
#2  row_upd (thr=0x7f4520022558) at storage/innobase/row/row0upd.c:2340
#3  row_upd_step (thr=0x7f4520022558) at storage/innobase/row/row0upd.c:2457
#4  0x000000000084ca71 in row_update_for_mysql (mysql_rec=<value optimized out>, prebuilt=0x7f452000b328)
    at storage/innobase/row/row0mysql.c:1462
#5  0x00000000008328b1 in ha_innobase::update_row (this=0x7f45200085d0, old_row=0x7f452000bf38 "\361\v", new_row=0x7f452000bf20 "\361\v")
    at storage/innobase/handler/ha_innodb.cc:6042
#6  0x000000000068e48a in handler::ha_update_row (this=0x7f45200085d0, old_data=0x7f452000bf38 "\361\v", new_data=0x7f452000bf20 "\361\v")
    at sql/handler.cc:5031
#7  0x00000000005f8139 in mysql_update (thd=0x6e01ad0, table_list=0x7f4520004cb0, fields=..., values=..., conds=0x7f45200056a0, order_num=<value optimized out>, 
    order=0x7f452db70700, limit=18446744073709551615, handle_duplicates=DUP_ERROR, ignore=false, found_return=0x7f452db713d0, updated_return=0x7f452db713c8)
    at sql/sql_update.cc:713

接update阶段2

二级索引记录加锁LOCK_X(LOCK_REC_NOT_GAP)（index->name = idx_a_b）

#0  lock_rec_lock (impl=1, mode=1027, block=0x7f4543968080, heap_no=3, index=0x7f4520024a18, thr=0x7f4520022558)
    at storage/innobase/lock/lock0lock.c:2118
#1  0x00000000009175c0 in lock_sec_rec_modify_check_and_lock (flags=<value optimized out>, block=0x7f4543968080, rec=<value optimized out>, index=0x7f4520024a18, 
    thr=0x7f4520022558, mtr=0x7f452db6fcb0) at storage/innobase/lock/lock0lock.c:5377
#2  0x000000000089e1bc in btr_cur_del_mark_set_sec_rec (flags=<value optimized out>, cursor=<value optimized out>, val=<value optimized out>, 
    thr=<value optimized out>, mtr=0x7f452db6fcb0) at storage/innobase/btr/btr0cur.c:2969
#3  0x0000000000861133 in row_upd_sec_index_entry (node=0x7f4520022258, thr=0x7f4520022558)
    at storage/innobase/row/row0upd.c:1648
#4  0x0000000000862337 in row_upd_sec_step (thr=0x7f4520022558) at storage/innobase/row/row0upd.c:1710
#5  row_upd (thr=0x7f4520022558) at storage/innobase/row/row0upd.c:2340
#6  row_upd_step (thr=0x7f4520022558) at storage/innobase/row/row0upd.c:2457
#7  0x000000000084ca71 in row_update_for_mysql (mysql_rec=<value optimized out>, prebuilt=0x7f452000b328)
    at storage/innobase/row/row0mysql.c:1462
#8  0x00000000008328b1 in ha_innobase::update_row (this=0x7f45200085d0, old_row=0x7f452000bf38 "\361\v", new_row=0x7f452000bf20 "\361\v")
    at storage/innobase/handler/ha_innodb.cc:6042
#9  0x000000000068e48a in handler::ha_update_row (this=0x7f45200085d0, old_data=0x7f452000bf38 "\361\v", new_data=0x7f452000bf20 "\361\v")
    at sql/handler.cc:5031
#10 0x00000000005f8139 in mysql_update (thd=0x6e01ad0, table_list=0x7f4520004cb0, fields=..., values=..., conds=0x7f45200056a0, order_num=<value optimized out>, 
    order=0x7f452db70700, limit=18446744073709551615, handle_duplicates=DUP_ERROR, ignore=false, found_return=0x7f452db713d0, updated_return=0x7f452db713c8)
    at sql/sql_update.cc:713

select阶段结束

锁住最后一条记录的下一条记录的间隙LOCK_X（LOCK_GAP），防止select阶段有数据插入（index->name=idx_b）

#0  lock_rec_lock (impl=0, mode=515, block=0x7f4543967d00, heap_no=4, index=0x7f4520023b68, thr=0x7f452000dd68)
    at storage/innobase/lock/lock0lock.c:2118
#1  0x0000000000917397 in lock_sec_rec_read_check_and_lock (flags=<value optimized out>, block=0x7f4543967d00, rec=0x7f45c1af409a "\200", index=0x7f4520023b68, 
    offsets=0x7f452db6ffc0, mode=<value optimized out>, gap_mode=512, thr=0x7f452000dd68)
    at storage/innobase/lock/lock0lock.c:5477
#2  0x0000000000856137 in sel_set_rec_lock (block=0x7f4543967d00, rec=0x7f45c1af409a "\200", index=0x7f4520023b68, offsets=0x7f452db6ffc0, 
    mode=<value optimized out>, type=<value optimized out>, thr=0x7f452000dd68)
    at storage/innobase/row/row0sel.c:1007
#3  0x000000000085a681 in row_search_for_mysql (buf=0x7f452000bf20 "\361\v", mode=2, prebuilt=<value optimized out>, match_mode=1, direction=<value optimized out>)
    at storage/innobase/row/row0sel.c:4207
#4  0x000000000083211e in ha_innobase::general_fetch (this=0x7f45200085d0, buf=0x7f452000bf20 "\361\v", direction=1, match_mode=1)
    at storage/innobase/handler/ha_innodb.cc:6730
#5  0x000000000068a68d in handler::read_multi_range_next (this=0x7f45200085d0, found_range_p=0x7f452db70418)
    at sql/handler.cc:4443
#6  0x00000000007450a1 in QUICK_RANGE_SELECT::get_next (this=0x7f4520023370) at sql/opt_range.cc:8684
#7  0x000000000075f12b in rr_quick (info=0x7f452db70700) at sql/records.cc:344
#8  0x00000000005f7b36 in mysql_update (thd=0x6e01ad0, table_list=0x7f4520004cb0, fields=..., values=..., conds=0x7f45200056a0, order_num=<value optimized out>, 
    order=0x7f452db70700, limit=18446744073709551614, handle_duplicates=DUP_ERROR, ignore=false, found_return=0x7f452db713d0, updated_return=0x7f452db713c8)
    at sql/sql_update.cc:644

总结

在InnoDB中，通过二级索引更新记录，首先会在WHERE条件使用到的二级索引上加Next-key类型的X锁，以防止查找记录期间的其它插入/删除记录，然后通过二级索引找到primary key并在primary key上加Record类型的X锁（之所以不是Next-key，是因为查询条件是二级索引，若WHERE条件使用到的是primary key，就会上Next-key类型的X锁），之后更新记录并检查更新字段是否是其它索引中的某列，如果存在这样的索引，通过update的旧值到二级索引中删除相应的entry，此时x锁类型为Record

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注